package com.interfaces;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.entity.Collector;
import com.entity.Measuringpoint;

/**
 * @Title: MeasuringpointDao.java
 * @Package com.interfaces
 * @author 孙梦楠
 * @date 2017年7月19日 下午4:06:54
 * @version V1.0.0
 */

public interface MeasuringpointDao extends JpaRepository<Measuringpoint, Integer> {

	Measuringpoint findByTerminalidAndMeasuringpointnumber(Integer terminalid, String measuringpointnumber);

	Measuringpoint findByTerminalidAndMeasuringpointordernumber(Integer terminalid, String measuringpointordernumber);

	List<Measuringpoint> findByCollectorid(Integer collectorid);
	
	List<Measuringpoint> findByTerminalid(Integer terminalid);
	
	Measuringpoint findByTerminalidAndMeterid(Integer terminalid,Integer meterid);
	
	Measuringpoint findByMeterid(Integer meterid);

	@Transactional
	@Query(value = "SELECT m FROM Measuringpoint m WHERE (m.terminalid=?1 OR ?1 IS NULL )"
			+ "AND (m.meterid=?2 OR ?2 IS NULL )" + "AND (m.collectorid=?3 OR ?3 IS NULL)"
			+ "AND (m.measuringpointnumber=?4 OR ?4 IS NULL)" + "AND (m.measuringpointordernumber=?5 OR ?5 IS NULL)"
			+ "AND (m.measuringpointport=?6 OR ?6 IS NULL)" + "AND (m.measuringpointbaud=?7 OR ?7 IS NULL)"
			+ "AND (m.measuringpointbigint=?8 OR ?8 IS NULL)" + "AND (m.measuringpointsmallint=?9 OR ?9 IS NULL)"
			+ "AND (m.measuringpointint=?10 OR ?10 IS NULL)" + "AND (m.measuringpointdec=?11 OR ?11 IS NULL)"
			+ "AND (m.terminalid IN (SELECT t FROM Terminal t WHERE (t.terminalorg=?12  OR ?12 IS NULL) AND (t.type = ?13)))")
	public List<Measuringpoint> findByConditions(Integer terminalid, Integer meterid, Integer collectorid,
			String measuringpointnumber, String measuringpointordernumber, String measuringpointport,
			String measuringpointbaud, String measuringpointbigint, String measuringpointsmallint,
			String measuringpointint, String measuringpointdec, Integer terminalorg,String type);

	// 符合org的所有采集点
	@Transactional
	@Query(value = "SELECT m FROM Measuringpoint m WHERE m.terminalid IN (SELECT t FROM Terminal t WHERE t.terminalorg=?1 AND t .type = ?2) ")
	public List<Measuringpoint> findByOrg(Integer terminalorg,String type);
	
	@Transactional
	@Query(value = "SELECT m FROM Measuringpoint m WHERE m.terminalid IN (SELECT t FROM Terminal t WHERE t .type = ?1) ")
	public List<Measuringpoint> findByType(String type);

	// 查单个集中器下的采集器数量
	@Transactional
	@Query(value = "SELECT COUNT(DISTINCT m.collectorid) FROM Measuringpoint m WHERE m.terminalid=?1 ")
	public Integer countCollectorByTerminal(Integer terminalid);


	// 查单个集中器下的采集器信息
	@Transactional
	@Query(value = "SELECT * FROM e_measuringpoint m WHERE m.terminal_id=?1 AND m.collector_id IS NOT NULL GROUP BY m.collector_id LIMIT 0,100", nativeQuery = true)
	public List<Measuringpoint> listCollectorByTerminal100(Integer terminalid);

	// 查单个集中器下的单表数量
	@Transactional
	@Query(value = "SELECT COUNT(DISTINCT m.meterid) FROM Measuringpoint m WHERE m.terminalid=?1 AND m.collectorid =null")
	public Integer countMeterByTerminal(Integer terminalid);



	// 查单个集中器下的单表信息
	@Transactional
	@Query(value = "SELECT * FROM e_measuringpoint m WHERE m.terminal_id=?1 AND m.collector_id  IS null LIMIT 0,100", nativeQuery = true)
	public List<Measuringpoint> listMeterByTerminal100(Integer terminalid);

	// 查单个采集器下的单表数量
	@Transactional
	@Query(value = "SELECT COUNT(DISTINCT m.meterid) FROM Measuringpoint m WHERE m.collectorid=?1")
	public Integer countMeterByCollector(Integer collectorid);

	// 查单个采集器下的单表信息
	@Transactional
	@Query(value = "SELECT * FROM e_measuringpoint m WHERE m.collector_id=?1 LIMIT 0,100", nativeQuery = true)
	public List<Measuringpoint> listMeterByCollector100(Integer collectorid);
	
	//根据采集终端ID删除
	@Transactional
    @Modifying
    @Query("delete from Measuringpoint m where m.terminalid=?1")
    List<Measuringpoint> deleteByTerminalid(Integer terminalid);
	
	//根据采集器ID删除
	@Transactional
    @Modifying
    @Query("delete from Measuringpoint m where m.collectorid=?1")
    List<Measuringpoint> deleteByCollectorid(Integer collectorid);
	
	//根据表ID删除
	@Transactional
    @Modifying
    @Query("delete from Measuringpoint m where m.meterid=?1")
    public void  deleteByMeterid(Integer meterid);
	
	

}
